-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE Correction 
	@StringResult varchar(max)
AS
BEGIN
	IF EXISTS (SELECT name FROM tempdb.dbo.sysobjects WHERE name = 'tabletemp' AND type = 'U')
			Begin
				Drop table tempdb.dbo.tabletemp					
			END		
	If NOT EXISTS (SELECT name FROM tempdb.dbo.sysobjects WHERE name = 'tabletemp' AND type = 'U')
		Begin
				create table tempdb.dbo.tabletemp (reponse varchar(5))
		end

	while charindex(',', @stringResult) > 0
	begin
		Insert into tempdb.dbo.tabletemp values (substring(@stringResult, 0, charindex(',', @stringResult))) 
		set @stringResult = SUBSTRING(@stringResult, charindex(',', @stringResult)+1, LEN(@stringResult))
	end
	Insert into tempdb.dbo.tabletemp values (substring(@stringResult, 1, LEN(@stringResult))) 

	--select * from tempdb.dbo.tabletemp
	select sum(correct.note) as NoteExamen
	from
	(
		select 
			case sum(r.Valeur) 
				when 100 then 1
				else -2
			end as note
		from tempdb.dbo.tabletemp tmp
		inner join Reponses r on r.Id = tmp.reponse
		group by r.IdQuestion
	)
	as correct
END
GO
